﻿using GBT.ParamEntity;
using GBT.SystemBusinessCore;
using GBT.SystemEnties;
using GBT.SystemEnties.DTO;
using GBT.Tool.Core;
using Newtonsoft.Json.Linq;
using NLog;
using Persistence.Core;
using SqlSugar;
using System;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace QueryDataLogic.Core
{
    public class MealReport
    {
        private static Logger logger = LogManager.GetCurrentClassLogger();
        ConfigManage cm = new ConfigManage();
        QueryData qd = new QueryData();
        DataPaging dp = new DataPaging();
        BasicInfo bi = new BasicInfo();

        /// <summary>
        /// 处理报餐信息（一天的三餐合并）食堂不同分开 （首页使用）
        /// </summary>
        /// <param name="ja"></param>
        /// <param name="districtId"></param>
        /// <returns></returns>
        private JArray MealHandle(JArray ja, int districtId)
        {
            JArray jaResult = new JArray();
            JObject joItem = new JObject();
            JArray jaQRCode = new JArray();
            JObject joQRCode = new JObject();
            string date = "", currentDate = "";
            string mealName = "", currentMealName = "";
            string hallNumber = "", currentHallNumber = "";
            int count = 0;//计数器
            int id = 0;
            string mid = "";
            //string idNumber = "";

            //返回值处理
            for (int jaIndex = 0; jaIndex < ja.Count; jaIndex++)
            {
                joQRCode = new JObject();
                //本次循环的 餐点和日期
                currentMealName = (string)ja[jaIndex]["mealName"];
                currentDate = (string)ja[jaIndex]["date"];

                currentHallNumber = (string)ja[jaIndex]["hallNumber"];

                id = (int)ja[jaIndex]["id"];
                mid = (string)ja[jaIndex]["mid"];
                //idNumber = dp.GetIdNumber(mid.ToString());
                joQRCode["idNumber"] = mid;
                joQRCode["id"] = id;
                joQRCode["date"] = (string)ja[jaIndex]["date"];
                joQRCode["mealNumber"] = (string)ja[jaIndex]["mealNumber"];
                joQRCode["mealName"] = (string)ja[jaIndex]["mealName"];
                joQRCode["hallNumber"] = (string)ja[jaIndex]["hallNumber"];
                joQRCode["remark"] = (string)ja[jaIndex]["remark"];
                joQRCode["companyId"] = bi.GetCompanyID(districtId);
                jaQRCode.Add(joQRCode);

                //下一次循环的 餐点和日期
                //判断不是最后一次循环
                if (jaIndex < ja.Count - 1)
                {
                    mealName = (string)ja[jaIndex + 1]["mealName"];
                    date = (string)ja[jaIndex + 1]["date"];
                    hallNumber = (string)ja[jaIndex + 1]["hallNumber"];
                }
                else
                {
                    //最后一次循环 清空
                    mealName = "";
                    date = "";
                }

                if (currentDate == date && currentHallNumber == hallNumber)
                {
                    if (count == 0)
                    {
                        joItem = new JObject();
                        joItem = (JObject)ja[jaIndex];

                    }
                    else
                    {
                        joItem["mealName"] = (string)joItem["mealName"] + "," + (string)ja[jaIndex]["mealName"];
                        joItem["qty"] = (int)joItem["qty"] + (int)ja[jaIndex]["qty"];
                        joItem["amount"] = (int)joItem["amount"] + (int)ja[jaIndex]["amount"];
                    }
                    count++;
                }
                else
                {
                    if (count == 0)
                    {
                        joItem = new JObject();
                        joItem = (JObject)ja[jaIndex];
                        joItem["shortName"] = ((string)joItem["mealName"]).Replace("餐", "");
                        joItem["qrcode"] = jaQRCode;
                        jaQRCode = new JArray();
                    }
                    else
                    {
                        //最后一次累加
                        joItem["mealName"] = (string)joItem["mealName"] + "," + (string)ja[jaIndex]["mealName"];
                        joItem["qty"] = (int)joItem["qty"] + (int)ja[jaIndex]["qty"];
                        joItem["amount"] = (int)joItem["amount"] + (int)ja[jaIndex]["amount"];
                        joItem["shortName"] = ((string)joItem["mealName"]).Replace("餐", "");
                        joItem["qrcode"] = jaQRCode;
                        jaQRCode = new JArray();
                    }
                    jaResult.Add(joItem);
                    count = 0;//计数器清零
                }

            }
            return jaResult;
        }

        /// <summary>
        /// 获取报餐信息 食堂不同分开 （首页使用）
        /// </summary>
        /// <param name="joParam"></param>
        /// <returns></returns>
        public JObject GetMealReport(JObject joParam)
        {
            JObject joResult = new JObject();
            try
            {
                int UserID = (int)joParam["userID"];
                int districtID = (int)joParam["districtID"];
                string dbName = bi.GetDBName(districtID);

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "SELECT rm.id, rm.mid, u.mobile,u.number,u.name,dh.hallNumber,dh.hallName,m.mealNumber,m.mealName,rm.[date], \r\n" +
                                    "rm.qty, (rm.price * rm.qty) AS amount, rm.creationDate,rm.remark,rm.creatorId FROM GBT_View_MealsReport rm \r\n" +
                                    "INNER JOIN MealUser u ON u.UserID = rm.UserID \r\n" +
                                    "INNER JOIN DiningHall dh ON dh.HallID = rm.HallID \r\n" +
                                    "LEFT JOIN MealTime m ON m.MealID = rm.MealID AND u.DistrictID = @districtID \r\n" +
                                    "WHERE u.UserID = @UserID AND rm.[date] >= CONVERT(varchar(100), getdate(), 23) " +
                                    "ORDER BY rm.[date] "
                };

                comm.Parameters.AddWithValue("@UserID", UserID);
                comm.Parameters.AddWithValue("@districtID", districtID);

                JArray ja = qd.GetData(comm, dbName);
                if (ja.Count > 0)
                {
                    joResult.Add("data", MealHandle(ja, districtID));
                    joResult.Add("message", "获取报餐信息成功。");
                    joResult.Add("state", true);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "获取报餐信息失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", "服务异常 " + ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }

        /// <summary>
        /// 处理报餐信息（一天的三餐合并）食堂不同 不分开（报餐页面使用）
        /// </summary>
        /// <param name="ja"></param>
        /// <returns></returns>
        private JArray MealAllHallHandle(JArray ja)
        {
            JArray jaResult = new JArray();
            JObject joItem = new JObject();
            string date = "", currentDate = "";
            string mealName = "", currentMealName = "";
            string hallNumber = "", currentHallNumber = "";
            int count = 0;//计数器
                          //返回值处理
            for (int jaIndex = 0; jaIndex < ja.Count; jaIndex++)
            {
                //本次循环的 餐点和日期
                currentMealName = (string)ja[jaIndex]["mealName"];
                currentDate = (string)ja[jaIndex]["date"];
                currentHallNumber = (string)ja[jaIndex]["hallNumber"];

                //下一次循环的 餐点和日期
                //判断不是最后一次循环
                if (jaIndex < ja.Count - 1)
                {
                    mealName = (string)ja[jaIndex + 1]["mealName"];
                    date = (string)ja[jaIndex + 1]["date"];

                    hallNumber = (string)ja[jaIndex + 1]["hallNumber"];
                }
                else
                {
                    //最后一次循环 清空
                    mealName = "";
                    date = "";
                }

                if (currentDate == date)
                {
                    if (count == 0)
                    {
                        joItem = new JObject();
                        joItem = (JObject)ja[jaIndex];
                    }
                    else
                    {
                        joItem["mealName"] = (string)joItem["mealName"] + "," + (string)ja[jaIndex]["mealName"];
                        joItem["qty"] = (int)joItem["qty"] + (int)ja[jaIndex]["qty"];
                        joItem["amount"] = (int)joItem["amount"] + (int)ja[jaIndex]["amount"];
                    }
                    count++;
                }
                else
                {
                    if (count == 0)
                    {
                        joItem = new JObject();
                        joItem = (JObject)ja[jaIndex];
                        joItem["shortName"] = ((string)joItem["mealName"]).Replace("餐", "").Replace("宵", "");
                    }
                    else
                    {
                        //最后一次累加
                        joItem["mealName"] = (string)joItem["mealName"] + "," + (string)ja[jaIndex]["mealName"];
                        joItem["qty"] = (int)joItem["qty"] + (int)ja[jaIndex]["qty"];
                        joItem["amount"] = (int)joItem["amount"] + (int)ja[jaIndex]["amount"];
                        joItem["shortName"] = ((string)joItem["mealName"]).Replace("餐", "").Replace("宵", "");
                    }
                    jaResult.Add(joItem);
                    count = 0;//计数器清零
                }

            }
            return jaResult;
        }

        /// <summary>
        /// 获取报餐信息 不同食堂不分开（报餐页面使用）
        /// </summary>
        /// <param name="joParam"></param>
        /// <returns></returns>
        public JObject GetMealAllHallReport(JObject joParam)
        {
            JObject joResult = new JObject();
            try
            {
                int UserID = (int)joParam["userID"];
                int districtID = (int)joParam["districtID"];
                string dbName = bi.GetDBName(districtID);

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "SELECT u.mobile,u.number,u.name,dh.hallNumber,dh.hallName,m.mealNumber,m.mealName,rm.[date], \r\n" +
                                    "rm.qty, (m.price * rm.qty) AS amount, rm.creationDate FROM ReportMeals rm \r\n" +
                                    "INNER JOIN MealUser u ON u.UserID = rm.UserID \r\n" +
                                    "INNER JOIN DiningHall dh ON dh.HallID = rm.HallID \r\n" +
                                    "INNER JOIN MealTime m ON m.MealID = rm.MealID \r\n" +
                                    "WHERE u.UserID = @UserID AND rm.[date] >= CONVERT(varchar(100), getdate(), 23) "
                };

                comm.Parameters.AddWithValue("@UserID", UserID);
                comm.Parameters.AddWithValue("@districtID", districtID);

                JArray ja = qd.GetData(comm, dbName);
                if (ja.Count > 0)
                {
                    joResult.Add("data", MealAllHallHandle(ja));
                    joResult.Add("message", "获取报餐信息成功。");
                    joResult.Add("state", true);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "获取报餐信息失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", "服务异常 " + ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }

        /// <summary>
        /// 我的报餐
        /// </summary>
        /// <param name="joParam"></param>
        /// <returns></returns>
        public JObject GetMealReportSelectDate(JObject joParam)
        {
            JObject joResult = new JObject();
            try
            {
                int UserID = (int)joParam["userID"];
                string StartDate = (string)joParam["startDate"];
                string EndDate = (string)joParam["endDate"];
                string hallName = (string)joParam["hallName"];
                int CurrentPage = (int)joParam["currentPage"];
                int districtID = (int)joParam["districtID"];
                string dbName = bi.GetDBName(districtID);

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "SELECT u.mobile,u.number,u.name,dh.hallNumber,dh.hallName,m.mealNumber,m.mealName,rm.[date], \r\n" +
                                    "rm.qty, (rm.price * rm.qty) AS amount, rm.creationDate FROM GBT_View_MealsReport rm \r\n" +
                                    "INNER JOIN MealUser u ON u.UserID = rm.UserID \r\n" +
                                    "INNER JOIN DiningHall dh ON dh.HallID = rm.HallID \r\n" +
                                    "INNER JOIN MealTime m ON m.MealID = rm.MealID \r\n" +
                                    "WHERE u.UserID = @UserID AND rm.[date] between @StartDate and @EndDate AND dh.hallName LIKE @hallName " +
                                    "ORDER BY rm.[date] "
                };

                comm.Parameters.AddWithValue("@UserID", UserID);
                comm.Parameters.AddWithValue("@StartDate", StartDate);
                comm.Parameters.AddWithValue("@EndDate", EndDate);
                comm.Parameters.AddWithValue("@hallName", "%" + hallName + "%");

                JArray ja = qd.GetData(comm, dbName);
                if (ja.Count > 0)
                {
                    joResult = dp.PagingProcessing(CurrentPage, ja);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "获取报餐信息失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", "服务异常 " + ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }

        /// <summary>
        /// 获取我的报餐
        /// </summary>
        /// <param name="_ReportMeals"></param>
        /// <returns></returns>
        public object GetMyMealReport(System_ReportMeals _ReportMeals)
        {
            var dbName = bi.GetDBName(_ReportMeals.districtID);
            DbContext<ReportMeals> dbContext = new DbContext<ReportMeals>(dbName);
            var db = dbContext.Db;
            var myMealReprotList = db.Queryable<ReportMeals, MealTime, DiningHall, MealUser>((rm, mt, dh, mu) => new object[] {
                JoinType.Left, rm.MealID == mt.MealId,
                JoinType.Left, rm.HallId == dh.HallId,
                JoinType.Left, rm.UserID == mu.UserID,
            })
            .Where((rm, mt, dh, mu) => rm.UserID == _ReportMeals.userID && SqlFunc.Between(rm.Date, _ReportMeals.startDate, _ReportMeals.endDate))
            .WhereIF(!string.IsNullOrEmpty(_ReportMeals.hallName), (rm, mt, dh, mu) => dh.HallName.Contains(_ReportMeals.hallName))
            .Select((rm, mt, dh, mu) => new {
                rm.ID,
                rm.DistrictID,
                rm.HallId,
                rm.MealID,
                rm.UserID,
                rm.month,
                rm.Date,
                rm.qty,
                rm.dayIndex,
                rm.IsTakeMeals,
                rm.CreationDate,
                mt.MealNumber,
                mt.MealName,
                mt.Price,
                dh.HallNumber,
                dh.HallName,
                mu.Number,
                mu.Name,
                mu.nickName,
                mu.mobile,
                amount = mt.Price * rm.qty,
            })
            .ToList();
            return myMealReprotList;
        }

        /// <summary>
        /// 报餐明细 (所有用户)
        /// </summary>
        /// <param name="joParam"></param>
        /// <returns></returns>
        public JObject GetMealReportSelectDateAll(JObject joParam)
        {
            JObject joResult = new JObject();
            try
            {
                string StartDate = (string)joParam["startDate"];
                string EndDate = (string)joParam["endDate"];
                string hallName = (string)joParam["hallName"];
                string MealName = (string)joParam["mealName"];
                int districtID = (int)joParam["districtID"];
                int CurrentPage = (int)joParam["currentPage"];
                string dbName = bi.GetDBName(districtID);

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "SELECT u.mobile,u.number,u.name,dh.hallNumber,dh.hallName,m.mealNumber,m.mealName,rm.[date], \r\n" +
                                    "rm.qty, (rm.price * rm.qty) AS amount, rm.creationDate,rm.remark FROM GBT_View_MealsReport rm \r\n" +
                                    "INNER JOIN MealUser u ON u.UserID = rm.UserID \r\n" +
                                    "INNER JOIN DiningHall dh ON dh.HallID = rm.HallID \r\n" +
                                    "INNER JOIN MealTime m ON m.MealID = rm.MealID \r\n" +
                                    "WHERE rm.[date] between @StartDate and @EndDate AND dh.hallName LIKE @hallName AND m.MealName LIKE @MealName \r\n" +
                                    "AND u.districtID = @districtID " +
                                    "ORDER BY rm.[date] "
                };

                comm.Parameters.AddWithValue("@StartDate", StartDate);
                comm.Parameters.AddWithValue("@EndDate", EndDate);
                comm.Parameters.AddWithValue("@hallName", "%" + hallName + "%");
                comm.Parameters.AddWithValue("@MealName", "%" + MealName + "%");
                comm.Parameters.AddWithValue("@districtID", districtID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    joResult = dp.PagingProcessing(CurrentPage, ja);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "获取报餐信息失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", "服务异常 " + ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }

        /// <summary>
        /// 报餐明细 (所有用户) 桌面端专用 
        /// </summary>
        /// <param name="joParam"></param>
        /// <returns></returns>
        public JObject GetMealReportSelectDateAllToWindows(JObject joParam)
        {
            JObject joResult = new JObject();
            try
            {
                string StartDate = (string)joParam["startDate"];
                string EndDate = (string)joParam["endDate"];
                string hallName = (string)joParam["hallName"];
                string MealName = (string)joParam["mealName"];
                int districtID = (int)joParam["districtID"];
                string dbName = bi.GetDBName(districtID);

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "SELECT u.mobile,u.number,u.name,dh.hallNumber,dh.hallName,m.mealNumber,m.mealName,rm.[date], \r\n" +
                                    "rm.qty, (rm.price * rm.qty) AS amount, rm.creationDate,rm.remark FROM GBT_View_MealsReport rm \r\n" +
                                    "INNER JOIN MealUser u ON u.UserID = rm.UserID \r\n" +
                                    "INNER JOIN DiningHall dh ON dh.HallID = rm.HallID \r\n" +
                                    "INNER JOIN MealTime m ON m.MealID = rm.MealID \r\n" +
                                    "WHERE rm.[date] between @StartDate and @EndDate AND dh.hallName LIKE @hallName AND m.MealName LIKE @MealName \r\n" +
                                    "AND u.districtID = @districtID " +
                                    "ORDER BY rm.[date] "
                };

                comm.Parameters.AddWithValue("@StartDate", StartDate);
                comm.Parameters.AddWithValue("@EndDate", EndDate);
                comm.Parameters.AddWithValue("@hallName", "%" + hallName + "%");
                comm.Parameters.AddWithValue("@MealName", "%" + MealName + "%");
                comm.Parameters.AddWithValue("@districtID", districtID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    joResult.Add("data", ja);
                    joResult.Add("message", "获取报餐信息成功。");
                    joResult.Add("state", true);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "获取报餐信息失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", "服务异常 " + ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }

        /// <summary>
        /// 报餐明细（所有用户）ORM重写
        /// </summary>
        /// <param name="mealTime"></param>
        /// <param name="totalNumber"></param>
        /// <param name="totalPage"></param>
        /// <returns></returns>
        public object GetAllMealReportInfo(System_MealTime mealTime, ref int totalNumber, ref int totalPage)
        {
            object result = null;

            try
            {
                var DBName = bi.GetDBName((int)mealTime.DistrictID);
                GBT.SystemBusinessCore.DbContext<MealTime> dbContext = new GBT.SystemBusinessCore.DbContext<MealTime>(DBName);
                Manage_District mDistrict = new Manage_District();
                mDistrict.DistrictID = mealTime.DistrictID;
                var district = bi.GetDistrictInfo(mDistrict);
                var db = dbContext.Db;
                var AllMealReport = db.Queryable<GBT_View_MealsReport, MealUser, DiningHall, MealTime, AddressBook>((gvmr, mu, dh, mt, ab) => new object[] {
                    JoinType.Left, gvmr.UserID == mu.UserID,
                    JoinType.Left, gvmr.HallID == dh.HallId,
                    JoinType.Left, gvmr.MealID == mt.MealId,
                    JoinType.Left, mu.AddressBookID == ab.ID,
                })
                .Where((gvmr, mu, dh, mt, ab) => mu.DistrictID == mealTime.DistrictID)
                .WhereIF(mealTime.startTime != null && mealTime.endTime != null, (gvmr, mu, dh, mt, ab) => SqlFunc.Between(gvmr.date, mealTime.startTime, mealTime.endTime))
                .WhereIF(mealTime.UserID != null, (gvmr, mu, dh, mt, ab) => gvmr.UserID == mealTime.UserID)
                .WhereIF(mealTime.HallID != null, (gvmr, mu, dh, mt, ab) => gvmr.HallID == mealTime.HallID)
                .WhereIF(mealTime.MealId != null, (gvmr, mu, dh, mt, ab) => gvmr.MealID == mealTime.MealId)
                .OrderBy((gvmr, mu, dh, mt, ab) => gvmr.HallID)
                .OrderBy((gvmr, mu, dh, mt, ab) => gvmr.date, OrderByType.Desc)
                .Select((gvmr, mu, dh, mt, ab) => new
                {
                    mu.mobile,
                    ab.Number,
                    ab.name,
                    dh.HallNumber,
                    dh.HallName,
                    mt.MealNumber,
                    mt.MealName,
                    gvmr.date,
                    gvmr.qty,
                    gvmr.price,
                    gvmr.CreationDate,
                    mu.DistrictID,
                    gvmr.remark,
                }).ToPageList(mealTime.PageIndex, mealTime.PageSize, ref totalNumber, ref totalPage);

                var AllMealReportNew = AllMealReport.Select(t => new
                {
                    t.mobile,
                    t.Number,
                    t.name,
                    t.HallNumber,
                    t.HallName,
                    t.MealNumber,
                    t.MealName,
                    t.date,
                    t.qty,
                    t.price,
                    money = t.qty * t.price,
                    t.CreationDate,
                    district.DistrictNumber,
                    district.DistrictName,
                }).ToList();

                result = AllMealReportNew;
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
            }

            return result;
        }

        /// <summary>
        /// 报餐统计(每天)
        /// </summary>
        /// <param name="joParam"></param>
        /// <returns></returns>
        public JObject GetMealStatistics(JObject joParam)
        {
            JObject joResult = new JObject();
            try
            {
                int HallID = (int)joParam["hallID"];
                string date = (string)joParam["date"];
                int districtID = (int)joParam["districtID"];
                string dbName = bi.GetDBName(districtID);

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "SELECT dh.hallNumber,dh.hallName,m.mealNumber,m.mealName,sum(rm.qty) as qty,rm.[date] \r\n" +
                                    "FROM GBT_View_MealsReport rm \r\n" +
                                    "INNER JOIN MealUser u ON rm.UserID = u.UserID \r\n" +
                                    "INNER JOIN DiningHall dh ON dh.HallID = rm.HallID \r\n" +
                                    "INNER JOIN MealTime m ON m.MealID = rm.MealID \r\n" +
                                    "WHERE dh.HallID = @HallID AND rm.[date] = @date AND u.DistrictID = @districtID \r\n" +
                                    "GROUP BY dh.hallNumber,dh.hallName,m.mealNumber,m.mealName,rm.[date] "
                };

                comm.Parameters.AddWithValue("@HallID", HallID);
                comm.Parameters.AddWithValue("@date", date);
                comm.Parameters.AddWithValue("@districtID", districtID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    joResult.Add("data", ja);
                    joResult.Add("message", "获取报餐统计信息成功。");
                    joResult.Add("state", true);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "获取报餐统计信息失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", "服务异常 " + ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }

        /// <summary>
        /// 报餐统计(时间段查询)
        /// </summary>
        /// <param name="joParam"></param>
        /// <returns></returns>
        public JObject GetMealStatisticsSelectDate(JObject joParam)
        {
            JObject joResult = new JObject();
            try
            {
                int HallID = (int)joParam["hallID"];
                string StartDate = (string)joParam["startDate"];
                string EndDate = (string)joParam["endDate"];
                int districtID = (int)joParam["districtID"];
                int CurrentPage = (int)joParam["currentPage"];
                string dbName = bi.GetDBName(districtID);

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "SELECT dh.hallNumber,dh.hallName,m.mealNumber,m.mealName,sum(rm.qty) as qty,rm.[date] \r\n" +
                                    "FROM GBT_View_MealsReport rm \r\n" +
                                    "INNER JOIN MealUser u ON rm.UserID = u.UserID \r\n" +
                                    "INNER JOIN DiningHall dh ON dh.HallID = rm.HallID \r\n" +
                                    "INNER JOIN MealTime m ON m.MealID = rm.MealID \r\n" +
                                    "WHERE rm.[date] BETWEEN @StartDate AND @EndDate \r\n" +
                                    "AND dh.HallID = @HallID AND u.DistrictID = @districtID \r\n" +
                                    "GROUP BY dh.hallNumber,dh.hallName,m.mealNumber,m.mealName,rm.[date] \r\n" +
                                    "ORDER BY rm.[date] "
                };

                comm.Parameters.AddWithValue("@HallID", HallID);
                comm.Parameters.AddWithValue("@StartDate", StartDate);
                comm.Parameters.AddWithValue("@EndDate", EndDate);
                comm.Parameters.AddWithValue("@districtID", districtID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    joResult = dp.PagingProcessing(CurrentPage, ja);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "获取报餐统计信息失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", "服务异常 " + ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }

        /// <summary>
        /// 报餐统计 ORM重写
        /// </summary>
        /// <param name="mealTime"></param>
        /// <param name="totalNumber"></param>
        /// <param name="totalPage"></param>
        /// <returns></returns>
        public object GetAllMealStatisticsReportInfo(System_MealTime mealTime, ref int totalNumber, ref int totalPage)
        {
            object result = null;

            try
            {
                var DBName = bi.GetDBName((int)mealTime.DistrictID);
                GBT.SystemBusinessCore.DbContext<MealTime> dbContext = new GBT.SystemBusinessCore.DbContext<MealTime>(DBName);
                Manage_District mDistrict = new Manage_District();
                mDistrict.DistrictID = mealTime.DistrictID;
                var district = bi.GetDistrictInfo(mDistrict);
                var db = dbContext.Db;
                var AllMealReport = db.Queryable<GBT_View_MealsReport, MealUser, DiningHall, MealTime>((gvmr, mu, dh, mt) => new object[] {
                    JoinType.Left, gvmr.UserID == mu.UserID,
                    JoinType.Left, gvmr.HallID == dh.HallId,
                    JoinType.Left, gvmr.MealID == mt.MealId,
                })
                .Where((gvmr, mu, dh, mt) => mu.DistrictID == mealTime.DistrictID)
                .WhereIF(mealTime.startTime != null && mealTime.endTime != null, (gvmr, mu, dh, mt) => SqlFunc.Between(gvmr.date, mealTime.startTime, mealTime.endTime))
                .WhereIF(mealTime.HallID != null, (gvmr, mu, dh, mt) => gvmr.HallID == mealTime.HallID)
                .WhereIF(mealTime.MealId != null, (gvmr, mu, dh, mt) => gvmr.MealID == mealTime.MealId)
                .GroupBy((gvmr, mu, dh, mt) => new { dh.HallNumber, dh.HallName, mt.MealNumber, mt.MealName, gvmr.date })
                .OrderBy((gvmr, mu, dh, mt) => new { gvmr.HallID, gvmr.date })
                .Select((gvmr, mu, dh, mt) => new
                {
                    dh.HallNumber,
                    dh.HallName,
                    mt.MealNumber,
                    mt.MealName,
                    gvmr.date,
                    qty = SqlFunc.AggregateSum(gvmr.qty),
                }).ToPageList(mealTime.PageIndex, mealTime.PageSize, ref totalNumber, ref totalPage);

                var AllMealReportNew = AllMealReport.Select(t => new
                {
                    t.HallNumber,
                    t.HallName,
                    t.MealNumber,
                    t.MealName,
                    t.date,
                    t.qty,
                    district.DistrictNumber,
                    district.DistrictName,
                }).ToList();

                result = AllMealReportNew;
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
            }

            return result;
        }

        /// <summary>
        /// 获取 家属报餐 信息
        /// </summary>
        /// <param name="joParam"></param>
        /// <returns></returns>
        public JObject GetFamilyIncrementalMeal(JObject joParam)
        {
            JObject joResult = new JObject();
            try
            {
                int UserID = (int)joParam["userID"];
                int districtID = (int)joParam["districtID"];
                int CurrentPage = (int)joParam["currentPage"];
                string dbName = bi.GetDBName(districtID);

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "SELECT rm.id,rm.userID,u.mobile,u.number,u.name,dh.hallID,dh.hallNumber,dh.hallName,rm.mealID,m.mealNumber,m.mealName,rm.[date], \r\n" +
                                    "rm.qty, m.familyPrice, (m.FamilyPrice * rm.qty) AS amount, rm.creationDate, rm.districtID FROM IncrementalMeal rm \r\n" +
                                    "INNER JOIN MealUser u ON u.UserID = rm.UserID \r\n" +
                                    "INNER JOIN DiningHall dh ON dh.HallID = rm.HallID \r\n" +
                                    "INNER JOIN MealTime m ON m.MealID = rm.MealID \r\n" +
                                    "WHERE u.UserID = @UserID AND u.DistrictID = @districtID AND rm.Type = 2 AND rm.[date] >= CONVERT(varchar(100), getdate(), 23) " +
                                    "ORDER BY rm.[date] "
                };

                comm.Parameters.AddWithValue("@UserID", UserID);
                comm.Parameters.AddWithValue("@districtID", districtID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    joResult = dp.PagingProcessing(CurrentPage, ja);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "获取家属报餐信息失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", "服务异常 " + ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }

        /// <summary>
        /// 获取 临时报餐 信息
        /// </summary>
        /// <param name="joParam"></param>
        /// <returns></returns>
        public JObject GetInterimIncrementalMeal(JObject joParam)
        {
            JObject joResult = new JObject();
            try
            {
                int UserID = (int)joParam["userID"];
                int districtID = (int)joParam["districtID"];
                int CurrentPage = (int)joParam["currentPage"];
                string dbName = bi.GetDBName(districtID);

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "SELECT rm.id,rm.userID,u.mobile,u.number,u.name,dh.hallID,dh.hallNumber,dh.hallName,rm.mealID,m.mealNumber,m.mealName,rm.[date], \r\n" +
                                    "rm.qty, m.familyPrice, (m.FamilyPrice * rm.qty) AS amount, rm.creationDate,rm.districtID,ISNULL(rm.remark,'') AS remark FROM IncrementalMeal rm \r\n" +
                                    "INNER JOIN MealUser u ON u.UserID = rm.UserID \r\n" +
                                    "INNER JOIN DiningHall dh ON dh.HallID = rm.HallID \r\n" +
                                    "INNER JOIN MealTime m ON m.MealID = rm.MealID \r\n" +
                                    "WHERE rm.creatorId = @UserID AND rm.Type = 1 AND u.DistrictID = @districtID AND rm.[date] >= CONVERT(varchar(100), getdate(), 23) " +
                                    "ORDER BY rm.[date] "
                };

                comm.Parameters.AddWithValue("@UserID", UserID);
                comm.Parameters.AddWithValue("@districtID", districtID);
                JArray ja = qd.GetData(comm, dbName);
                if (ja.Count > 0)
                {
                    joResult = dp.PagingProcessing(CurrentPage, ja);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "获取临时报餐信息失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", "服务异常 " + ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }

        /// <summary>
        /// 获取 加班餐 信息
        /// </summary>
        /// <param name="joParam"></param>
        /// <returns></returns>
        public JObject GetWorkOvertimeMeal(JObject joParam)
        {
            JObject joResult = new JObject();
            try
            {
                int UserID = (int)joParam["userID"];
                int districtID = (int)joParam["districtID"];
                int CurrentPage = (int)joParam["currentPage"];
                string dbName = bi.GetDBName(districtID);

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "SELECT rm.id,rm.userID,u.mobile,u.number,u.name,dh.hallID,dh.hallNumber,dh.hallName,rm.mealID,m.mealNumber,m.mealName,rm.[date], \r\n" +
                                    "rm.qty, m.familyPrice, (m.FamilyPrice * rm.qty) AS amount, rm.creationDate,rm.districtID,ISNULL(rm.remark,'') AS remark,ISNULL(rm.isAudit,0) AS isAudit FROM IncrementalMeal rm \r\n" +
                                    "INNER JOIN MealUser u ON u.UserID = rm.UserID \r\n" +
                                    "INNER JOIN DiningHall dh ON dh.HallID = rm.HallID \r\n" +
                                    "INNER JOIN MealTime m ON m.MealID = rm.MealID \r\n" +
                                    "WHERE u.UserID = @UserID AND rm.Type = 3 AND u.DistrictID = @districtID AND rm.[date] >= CONVERT(varchar(100), getdate(), 23) " +
                                    "ORDER BY rm.[date] "
                };

                comm.Parameters.AddWithValue("@UserID", UserID);
                comm.Parameters.AddWithValue("@districtID", districtID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    joResult = dp.PagingProcessing(CurrentPage, ja);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "获取加班餐信息失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", "服务异常 " + ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }

        /// <summary>
        /// 已报餐已取餐报表
        /// </summary>
        /// <returns></returns>
        public async Task<object> GetTheMealAndTaskFood(TheMealAndTaskFood theMealAndTaskFood)
        {
            try
            {
                int totalNumber = 0;
                string dbName = bi.GetDBName(theMealAndTaskFood.DistrictID);
                var db = new GBT.SystemBusinessCore.DbContext<GBT_View_MealsReport>(dbName).Db;
                var theMealAndTaskFoodList = await db.Queryable<GBT_View_MealsReport, MealUser, MealTime, DiningHall>((vmr, mu, mt, dh) => new object[] {
                    JoinType.Left, vmr.UserID == mu.UserID,
                    JoinType.Left, vmr.MealID == mt.MealId,
                    JoinType.Left, vmr.HallID == dh.HallId,
                })
                .Where((vmr, mu, mt, dh) => mu.DistrictID == theMealAndTaskFood.DistrictID)
                .WhereIF(!string.IsNullOrEmpty(theMealAndTaskFood.Number), (vmr, mu, mt, dh) => mu.Number == theMealAndTaskFood.Number)
                .WhereIF(!string.IsNullOrEmpty(theMealAndTaskFood.Name), (vmr, mu, mt, dh) => mu.Name == theMealAndTaskFood.Name)
                .GroupBy((vmr, mu, mt, dh) => new { vmr.Type, vmr.UserID, vmr.date, vmr.MealID})
                .Select((vmr, mu, mt, dh) => new {
                    vmr.ID,
                    vmr.Type,
                    vmr.HallID,
                    vmr.MealID,
                    vmr.UserID,
                    mu.Number,
                    mu.Name,
                    mu.nickName,
                    mt.MealName,
                    qty = SqlFunc.AggregateSum(vmr.qty),
                    mt.Price,
                    mt.FamilyPrice,
                    dh.HallName,
                })
                .ToPageListAsync(theMealAndTaskFood.PageIndex, theMealAndTaskFood.PageSize, totalNumber);

                return theMealAndTaskFood;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
